using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN DanhSachDeNghiLuuBanDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:21/09/2010 46:01:44</para>
    /// <para>Class Name: DanhSachDeNghiLuuBanDAO</para>
    /// <para>Description: Class access to table DanhSachDeNghiLuuBan in database. Class for coder code here</para>
    ///</summary>


    public partial class DanhSachDeNghiLuuBanDAO
    {
        public DataSet SelectAllDSToCalculate(List<int>LsSinhVien_ID,int NamHoc_ID,int HocKy_ID,int ChuongTrinhDaoTao_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        SinhVien_ID,
                             (SELECT        COUNT(SinhVienMonHoc.SinhVienMonHoc_ID) AS SoMon
                               FROM            SinhVienMonHoc INNER JOIN
                                                         ChiTietChuongTrinh ON SinhVienMonHoc.MonHoc_ID = ChiTietChuongTrinh.MonHoc_ID
                               WHERE        (SinhVienMonHoc.HocKy_ID = @HocKy_ID) AND (SinhVienMonHoc.NamHoc_ID = @NamHoc_ID) AND 
                                                         (SinhVienMonHoc.SinhVien_ID = SinhVien.SinhVien_ID) AND (SinhVienMonHoc.IsDat = 0) AND (ChiTietChuongTrinh.BatBuocQuaTrongHocKy = 1) AND 
                                                         (ChiTietChuongTrinh.ChuongTrinhDaoTao_ID = @ChuongTrinhDaoTao_ID)) AS SMBBKDat,
                             (SELECT        SUM(MonHoc.SoHocPhan) AS SHP
                               FROM            SinhVienMonHoc AS SinhVienMonHoc_1 INNER JOIN
                                                         MonHoc ON SinhVienMonHoc_1.MonHoc_ID = MonHoc.MonHoc_ID
                               WHERE        (SinhVienMonHoc_1.HocKy_ID = @HocKy_ID) AND (SinhVienMonHoc_1.NamHoc_ID = @NamHoc_ID) AND 
                                                         (SinhVienMonHoc_1.SinhVien_ID = SinhVien.SinhVien_ID) AND (SinhVienMonHoc_1.IsDat = 0)) AS SoHPKoDat,
                              (SELECT        COUNT(SinhVienMonHoc.MonHoc_ID) AS SoMon
                               FROM            SinhVienMonHoc INNER JOIN ChiTietChuongTrinh ON SinhVienMonHoc.MonHoc_ID = ChiTietChuongTrinh.MonHoc_ID
                               WHERE             (SinhVienMonHoc.SinhVien_ID = SinhVien.SinhVien_ID) AND (SinhVienMonHoc.IsDat = 1) AND (ChiTietChuongTrinh.BatBuocQuaTrongHocKy = 1) AND 
                                                 (ChiTietChuongTrinh.ChuongTrinhDaoTao_ID = @ChuongTrinhDaoTao_ID)) AS SMHocDat,
                              FROM            SinhVien
                              WHERE SinhVien.SinhVien_ID IN(" + string.Join(",",LsSinhVien_ID.ConvertAll<string>(Convert.ToString).ToArray())+ @")
                                    AND SinhVien.SinhVien_ID NOT IN(SELECT DanhSachDeNghiLuuBan.SinhVien_ID FROM DanhSachDeNghiLuuBan WHERE DanhSachDeNghiLuuBan.NamHoc_ID=@NamHoc_ID AND DanhSachDeNghiLuuBan.HocKy_ID=@HocKy_ID)
                              GROUP BY SinhVien_ID";
            this.CreateCommandParameter(cmd, "@NamHoc_ID", NamHoc_ID);
            this.CreateCommandParameter(cmd, "@HocKy_ID", HocKy_ID);
            this.CreateCommandParameter(cmd, "@ChuongTrinhDaoTao_ID", ChuongTrinhDaoTao_ID);
            return this.SelectDS(cmd);
        }
        public DataSet SelectDSSoMonHocByChuongTrinhDaoTao(int NamHoc_ID, int HocKy_ID, int ChuongTrinhDaoTao_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT COUNT(ChiTietChuongTrinh.MonHoc_ID) AS SoMon,
                                       SUM(ChiTietChuongTrinh.SoHocPhan) AS SoHP
                               FROM    ChiTietChuongTrinh
                               WHERE   ChiTietChuongTrinh.HocKy_ID = @HocKy_ID 
                                       AND ChiTietChuongTrinh.NamHoc_ID = @NamHoc_ID 
                                       AND ChiTietChuongTrinh.BatBuocQuaTrongHocKy = 1
                                       AND ChiTietChuongTrinh.ChuongTrinhDaoTao_ID = @ChuongTrinhDaoTao_ID";
            this.CreateCommandParameter(cmd, "@NamHoc_ID", NamHoc_ID);
            this.CreateCommandParameter(cmd, "@HocKy_ID", HocKy_ID);
            this.CreateCommandParameter(cmd, "@ChuongTrinhDaoTao_ID", ChuongTrinhDaoTao_ID);
            return this.SelectDS(cmd);
        }
    }
    /*****************************************************************************************************/
    /**********************************END DanhSachDeNghiLuuBanDAO*****************************************/
    /*****************************************************************************************************/
}

